﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using DRHotel_v1.Classes;

namespace DRHotel_v1.Classes.DBManagers
{
    class HousekeepingDBManager
    {
        //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        // Item Management Manager
        //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        public static bool InsertItem(Item item)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();
            if (item != null)
            {

                try
                {
                    conn = new SqlConnection();
                    conn.ConnectionString = ConfigurationManager.
                        ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                    conn.Open();
                    comm = new SqlCommand();
                    comm.Connection = conn;
                    comm.CommandText = "insert into Item " +
                        "(ItemName, ItemPrice) values " +
                        "(@itemName,@itemPrice)";
                    comm.Parameters.AddWithValue("@itemName", item.itemName);
                    comm.Parameters.AddWithValue("@itemPrice", item.itemPrice);
                    int rows = comm.ExecuteNonQuery();
                    if (rows > 0)
                        result = true;
                }
                catch (SqlException ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }

        public static ArrayList GetItemList()
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList itemList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select Distinct * From Item";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Item item = new Item(Convert.ToInt32(dr["itemID"]),
                                             dr["itemName"].ToString(),
                                             Convert.ToInt32(dr["itemPrice"]));
                    itemList.Add(item);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return itemList;
        }

        public static ArrayList GetItemListByItemID(int itemID)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList itemList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Item Where itemID =" + itemID;
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Item item = new Item(Convert.ToInt32(dr["itemID"]),
                                             dr["itemName"].ToString(),
                                             Convert.ToInt32(dr["itemPrice"]));
                    itemList.Add(item);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return itemList;
        }

        public static ArrayList GetItemListByItemName(string itemName)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList itemList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Item Where itemName =" + "'" + itemName + "'";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Item item = new Item(Convert.ToInt32(dr["itemID"]),
                                             dr["itemName"].ToString(),
                                             Convert.ToInt32(dr["itemPrice"]));
                    itemList.Add(item);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return itemList;
        }

        public static ArrayList GetItemListByItemPrice(double itemPrice)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList itemList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Item Where itemPrice =" + itemPrice;
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Item item = new Item(Convert.ToInt32(dr["itemID"]),
                                             dr["itemName"].ToString(),
                                             Convert.ToInt32(dr["itemPrice"]));
                    itemList.Add(item);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return itemList;
        }

        public static ArrayList GetAllItemList()
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList itemList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Item";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Item item = new Item(Convert.ToInt32(dr["itemID"]),
                                             dr["itemName"].ToString(),
                                             Convert.ToInt32(dr["itemPrice"]));
                    itemList.Add(item);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return itemList;
        }

        public static Item GetItemByName(string itemName)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            Item item = new Item();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Item Where itemName =" + "'" + itemName + "'";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    item.itemID = Convert.ToInt32(dr["itemID"]);
                    item.itemName = dr["itemName"].ToString();
                    item.itemPrice = Convert.ToInt32(dr["itemPrice"]);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return item;
        }

        public static Item GetItemByID(int itemID)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            Item item = new Item();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Item Where itemID =" + itemID;
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    item.itemID = Convert.ToInt32(dr["itemID"]);
                    item.itemName = dr["itemName"].ToString();
                    item.itemPrice = Convert.ToInt32(dr["itemPrice"]);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return item;
        }

        public static int UpdateItemByID(int itemID, string itemName, double itemPrice)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            int rowsinserted = 0;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "UPDATE item SET itemName=@itemName, itemPrice=@itemPrice WHERE itemID = @itemID";
                cmd.Connection = conn;
                cmd.Parameters.AddWithValue("@itemID", itemID);
                cmd.Parameters.AddWithValue("@itemName", itemName);
                cmd.Parameters.AddWithValue("@itemPrice", itemPrice);
                rowsinserted = cmd.ExecuteNonQuery();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return rowsinserted;
        }

        //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        // Room Management Manager
        //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        public static bool InsertRoom(Room room)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();
            if (room != null)
            {

                try
                {
                    conn = new SqlConnection();
                    conn.ConnectionString = ConfigurationManager.
                        ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                    conn.Open();
                    comm = new SqlCommand();
                    comm.Connection = conn;
                    comm.CommandText = "insert into Room " +
                        "(RoomID, RoomType, BedType, Capacity, RoomRates, RoomStatus, Smoking) values " +
                        "(@roomID,@roomType, @bedType, @capacity, @roomRates, @roomStatus, @smoking)";
                    comm.Parameters.AddWithValue("@roomID", room.roomID);
                    comm.Parameters.AddWithValue("@roomType", room.roomType);
                    comm.Parameters.AddWithValue("@bedType", room.bedType);
                    comm.Parameters.AddWithValue("@capacity", room.capacity);
                    comm.Parameters.AddWithValue("@roomRates", room.roomRates);
                    comm.Parameters.AddWithValue("@roomStatus", room.roomStatus);
                    comm.Parameters.AddWithValue("@smoking", room.smoking);
                    int rows = comm.ExecuteNonQuery();
                    if (rows > 0)
                        result = true;
                }
                catch (SqlException ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }

        public static ArrayList GetRoomList()
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList roomList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select Distinct * From Room";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Room room = new Room(Convert.ToInt32(dr["roomID"]),
                                             dr["roomType"].ToString(),
                                             dr["bedType"].ToString(),
                                             Convert.ToInt32(dr["capacity"]),
                                             Convert.ToDouble(dr["roomRates"]),
                                             dr["roomStatus"].ToString(),
                                             dr["remark"].ToString(),
                                             Convert.ToBoolean(dr["smoking"].ToString()));
                    roomList.Add(room);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return roomList;
        }

        public static ArrayList GetAllRoomListByRoomType(string roomType)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList roomList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Room Where roomType =" + "'" + roomType + "'";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Room room = new Room(Convert.ToInt32(dr["roomID"]),
                                             dr["roomType"].ToString(),
                                             dr["bedType"].ToString(),
                                             Convert.ToInt32(dr["capacity"]),
                                             Convert.ToDouble(dr["roomRates"]),
                                             dr["roomStatus"].ToString(),
                                             dr["remark"].ToString(),
                                             Convert.ToBoolean(dr["smoking"].ToString()));
                    roomList.Add(room);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return roomList;
        }

        public static ArrayList GetAllRoomListByBedType(string bedType)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList roomList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Room Where bedType =" + "'" + bedType + "'";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Room room = new Room(Convert.ToInt32(dr["roomID"]),
                                             dr["roomType"].ToString(),
                                             dr["bedType"].ToString(),
                                             Convert.ToInt32(dr["capacity"]),
                                             Convert.ToDouble(dr["roomRates"]),
                                             dr["roomStatus"].ToString(),
                                             dr["remark"].ToString(),
                                             Convert.ToBoolean(dr["smoking"].ToString()));
                    roomList.Add(room);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return roomList;
        }

        public static ArrayList GetAllRoomListByRoomStatus(string roomStatus)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList roomList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Room Where roomStatus =" + "'" + roomStatus + "'";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Room room = new Room(Convert.ToInt32(dr["roomID"]),
                                             dr["roomType"].ToString(),
                                             dr["bedType"].ToString(),
                                             Convert.ToInt32(dr["capacity"]),
                                             Convert.ToDouble(dr["roomRates"]),
                                             dr["roomStatus"].ToString(),
                                             dr["remark"].ToString(),
                                             Convert.ToBoolean(dr["smoking"].ToString()));
                    roomList.Add(room);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return roomList;
        }

        public static ArrayList GetAllRoomListBySmoking(string smoking)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList roomList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Room Where smoking =" + "'" + smoking + "'";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Room room = new Room(Convert.ToInt32(dr["roomID"]),
                                             dr["roomType"].ToString(),
                                             dr["bedType"].ToString(),
                                             Convert.ToInt32(dr["capacity"]),
                                             Convert.ToDouble(dr["roomRates"]),
                                             dr["roomStatus"].ToString(),
                                             dr["remark"].ToString(),
                                             Convert.ToBoolean(dr["smoking"].ToString()));
                    roomList.Add(room);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return roomList;
        }

        public static ArrayList GetRoomListByRoomID(int roomID)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList roomList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Room Where roomID =" + roomID;
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Room room = new Room(Convert.ToInt32(dr["roomID"]),
                                             dr["roomType"].ToString(),
                                             dr["bedType"].ToString(),
                                             Convert.ToInt32(dr["capacity"]),
                                             Convert.ToDouble(dr["roomRates"]),
                                             dr["roomStatus"].ToString(),
                                             dr["remark"].ToString(),
                                             Convert.ToBoolean(dr["smoking"].ToString()));
                    roomList.Add(room);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return roomList;
        }

        public static ArrayList GetAllRoomList()
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList roomList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Room";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Room room = new Room(Convert.ToInt32(dr["roomID"]),
                                             dr["roomType"].ToString(),
                                             dr["bedType"].ToString(),
                                             Convert.ToInt32(dr["capacity"]),
                                             Convert.ToDouble(dr["roomRates"]),
                                             dr["roomStatus"].ToString(),
                                             dr["remark"].ToString(),
                                             Convert.ToBoolean(dr["smoking"].ToString()));
                    roomList.Add(room);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return roomList;
        }

        public static Room GetRoomByID(int roomID)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            Room room = new Room();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Room Where roomID =" + roomID;
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    room.roomID = Convert.ToInt32(dr["roomID"]);
                    room.roomType = dr["roomType"].ToString();
                    room.bedType = dr["bedType"].ToString();
                    room.capacity = Convert.ToInt32(dr["capacity"]);
                    room.roomRates = Convert.ToDouble(dr["roomRates"]);
                    room.roomStatus = dr["roomStatus"].ToString();
                    room.remark = dr["remark"].ToString();
                    room.smoking = Convert.ToBoolean(dr["smoking"]);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return room;
        }

        public static int UpdateRoomByID(int roomId, string roomType, string bedType, int capacity, double roomRates, string roomStatus, bool smoking)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            int rowsinserted = 0;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "UPDATE room SET roomType=@roomType, bedType=@bedType, capacity=@capacity, roomRates=@roomRates, roomStatus=@roomStatus, smoking=@smoking WHERE roomId = @roomId";
                cmd.Connection = conn;
                cmd.Parameters.AddWithValue("@roomId", roomId);
                cmd.Parameters.AddWithValue("@roomType", roomType);
                cmd.Parameters.AddWithValue("@bedType", bedType);
                cmd.Parameters.AddWithValue("@capacity", capacity);
                cmd.Parameters.AddWithValue("@roomRates", roomRates);
                cmd.Parameters.AddWithValue("@roomStatus", roomStatus);
                cmd.Parameters.AddWithValue("@smoking", smoking);
                rowsinserted = cmd.ExecuteNonQuery();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return rowsinserted;
        }

        //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        // Staff Management Manager
        //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        public static bool InsertStaff(Staff staff)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();
            if (staff != null)
            {

                try
                {
                    conn = new SqlConnection();
                    conn.ConnectionString = ConfigurationManager.
                        ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                    conn.Open();
                    comm = new SqlCommand();
                    comm.Connection = conn;
                    comm.CommandText = "insert into Staff " +
                        "(firstName, lastName, email, address, dob, gender, nationality, phoneNo, staffType, bankNo) values " +
                        "(@firstName, @lastName, @email, @address, @dob, @gender, @nationality, @phoneNo, @staffType, @bankNo)";
                    comm.Parameters.AddWithValue("@firstName", staff.firstName);
                    comm.Parameters.AddWithValue("@lastName", staff.lastName);
                    comm.Parameters.AddWithValue("@email", staff.email);
                    comm.Parameters.AddWithValue("@address", staff.address);
                    comm.Parameters.AddWithValue("@dob", staff.dob);
                    comm.Parameters.AddWithValue("@gender", staff.gender);
                    comm.Parameters.AddWithValue("@nationality", staff.nationality);
                    comm.Parameters.AddWithValue("@phoneNo", staff.phoneNo);
                    comm.Parameters.AddWithValue("@staffType", staff.staffType);
                    comm.Parameters.AddWithValue("@bankNo", staff.bankNo);
                    int rows = comm.ExecuteNonQuery();
                    if (rows > 0)
                        result = true;
                }
                catch (SqlException ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }

        public static ArrayList GetStaffList()
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList staffList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select Distinct * From Staff";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Staff staff = new Staff(Convert.ToInt32(dr["staffID"]),
                                             dr["firstName"].ToString(),
                                             dr["lastName"].ToString(),
                                             dr["email"].ToString(),
                                             dr["address"].ToString(),
                                             Convert.ToDateTime(dr["dob"].ToString()),
                                             Convert.ToBoolean(dr["gender"].ToString()),
                                             dr["nationality"].ToString(),
                                             dr["phoneNo"].ToString(),
                                             dr["staffType"].ToString(),
                                             dr["bankNo"].ToString());
                    staffList.Add(staff);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return staffList;
        }

        public static ArrayList GetStaffListByStaffID(int staffID)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList staffList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Staff Where staffID =" + staffID;
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Staff staff = new Staff(Convert.ToInt32(dr["staffID"]),
                                             dr["firstName"].ToString(),
                                             dr["lastName"].ToString(),
                                             dr["email"].ToString(),
                                             dr["address"].ToString(),
                                             Convert.ToDateTime(dr["dob"].ToString()),
                                             Convert.ToBoolean(dr["gender"].ToString()),
                                             dr["nationality"].ToString(),
                                             dr["phoneNo"].ToString(),
                                             dr["staffType"].ToString(),
                                             dr["bankNo"].ToString());
                    staffList.Add(staff);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return staffList;
        }

        public static ArrayList GetStaffListByStaffName(string firstName, string lastName)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList staffList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Staff Where firstName =" + "'" + firstName + "'And lastName =" + "'" + lastName + "'";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Staff staff = new Staff(Convert.ToInt32(dr["staffID"]),
                                             dr["firstName"].ToString(),
                                             dr["lastName"].ToString(),
                                             dr["email"].ToString(),
                                             dr["address"].ToString(),
                                             Convert.ToDateTime(dr["dob"].ToString()),
                                             Convert.ToBoolean(dr["gender"].ToString()),
                                             dr["nationality"].ToString(),
                                             dr["phoneNo"].ToString(),
                                             dr["staffType"].ToString(),
                                             dr["bankNo"].ToString());
                    staffList.Add(staff);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return staffList;
        }

        public static ArrayList GetStaffListByGender(string gender)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList staffList = new ArrayList();
            bool gender2 = false;
            if (gender.Equals("Male"))
            {
                gender2 = true;
            }
            else if (gender.Equals("Female"))
            {
                gender2 = false;
            }

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Staff Where gender =" + "'" + gender2 + "'";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Staff staff = new Staff(Convert.ToInt32(dr["staffID"]),
                                             dr["firstName"].ToString(),
                                             dr["lastName"].ToString(),
                                             dr["email"].ToString(),
                                             dr["address"].ToString(),
                                             Convert.ToDateTime(dr["dob"].ToString()),
                                             Convert.ToBoolean(dr["gender"].ToString()),
                                             dr["nationality"].ToString(),
                                             dr["phoneNo"].ToString(),
                                             dr["staffType"].ToString(),
                                             dr["bankNo"].ToString());
                    staffList.Add(staff);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return staffList;
        }

        public static ArrayList GetStaffListByType(string staffType)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList staffList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Staff Where staffType =" + "'" + staffType + "'";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Staff staff = new Staff(Convert.ToInt32(dr["staffID"]),
                                             dr["firstName"].ToString(),
                                             dr["lastName"].ToString(),
                                             dr["email"].ToString(),
                                             dr["address"].ToString(),
                                             Convert.ToDateTime(dr["dob"].ToString()),
                                             Convert.ToBoolean(dr["gender"].ToString()),
                                             dr["nationality"].ToString(),
                                             dr["phoneNo"].ToString(),
                                             dr["staffType"].ToString(),
                                             dr["bankNo"].ToString());
                    staffList.Add(staff);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return staffList;
        }

        public static ArrayList GetAllStaffList()
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList staffList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Staff";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Staff staff = new Staff(Convert.ToInt32(dr["staffID"]),
                                             dr["firstName"].ToString(),
                                             dr["lastName"].ToString(),
                                             dr["email"].ToString(),
                                             dr["address"].ToString(),
                                             Convert.ToDateTime(dr["dob"].ToString()),
                                             Convert.ToBoolean(dr["gender"].ToString()),
                                             dr["nationality"].ToString(),
                                             dr["phoneNo"].ToString(),
                                             dr["staffType"].ToString(),
                                             dr["bankNo"].ToString());
                    staffList.Add(staff);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return staffList;
        }

        public static Staff GetStaffByID(int staffID)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            Staff staff = new Staff();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Staff Where staffID =" + staffID;
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    staff.staffID = Convert.ToInt32(dr["staffID"]);
                    staff.firstName = dr["firstName"].ToString();
                    staff.lastName = dr["lastName"].ToString();
                    staff.email = dr["email"].ToString();
                    staff.address = dr["address"].ToString();
                    staff.dob = Convert.ToDateTime(dr["dob"].ToString());
                    staff.gender = Convert.ToBoolean(dr["gender"].ToString());
                    staff.nationality = dr["nationality"].ToString();
                    staff.phoneNo = dr["phoneNo"].ToString();
                    staff.staffType = dr["staffType"].ToString();
                    staff.bankNo = dr["bankNo"].ToString();
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return staff;
        }

        public static Staff GetStaffByName(string firstName, string lastName)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            Staff staff = new Staff();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Staff Where firstName =" + "'" + firstName + "'And lastName =" + "'" + lastName + "'";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    staff.staffID = Convert.ToInt32(dr["staffID"]);
                    staff.firstName = dr["firstName"].ToString();
                    staff.lastName = dr["lastName"].ToString();
                    staff.email = dr["email"].ToString();
                    staff.address = dr["address"].ToString();
                    staff.dob = Convert.ToDateTime(dr["dob"].ToString());
                    staff.gender = Convert.ToBoolean(dr["gender"].ToString());
                    staff.nationality = dr["nationality"].ToString();
                    staff.phoneNo = dr["phoneNo"].ToString();
                    staff.staffType = dr["staffType"].ToString();
                    staff.bankNo = dr["bankNo"].ToString();
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return staff;
        }

        public static Staff GetStaffByGender(string gender)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            Staff staff = new Staff();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Staff Where gender =" + "'" + gender + "'";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    staff.staffID = Convert.ToInt32(dr["staffID"]);
                    staff.firstName = dr["firstName"].ToString();
                    staff.lastName = dr["lastName"].ToString();
                    staff.email = dr["email"].ToString();
                    staff.address = dr["address"].ToString();
                    staff.dob = Convert.ToDateTime(dr["dob"].ToString());
                    staff.gender = Convert.ToBoolean(dr["gender"].ToString());
                    staff.nationality = dr["nationality"].ToString();
                    staff.phoneNo = dr["phoneNo"].ToString();
                    staff.staffType = dr["staffType"].ToString();
                    staff.bankNo = dr["bankNo"].ToString();
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return staff;
        }

        public static Staff GetStaffByType(string staffType)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            Staff staff = new Staff();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From Staff Where staffType =" + "'" + staffType + "'";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    staff.staffID = Convert.ToInt32(dr["staffID"]);
                    staff.firstName = dr["firstName"].ToString();
                    staff.lastName = dr["lastName"].ToString();
                    staff.email = dr["email"].ToString();
                    staff.address = dr["address"].ToString();
                    staff.dob = Convert.ToDateTime(dr["dob"].ToString());
                    staff.gender = Convert.ToBoolean(dr["gender"].ToString());
                    staff.nationality = dr["nationality"].ToString();
                    staff.phoneNo = dr["phoneNo"].ToString();
                    staff.staffType = dr["staffType"].ToString();
                    staff.bankNo = dr["bankNo"].ToString();
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return staff;
        }

        public static int UpdateStaffByID(int staffID, string firstName, string lastName, string email, string address, DateTime dob, bool gender, string nationality, string phoneNo, string staffType, string bankNo)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            int rowsinserted = 0;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "UPDATE staff SET firstName=@firstName, lastName=@lastName, email=@email, address=@address, dob=@dob, gender=@gender, nationality=@nationality, phoneNo=@phoneNo, staffType=@staffType, bankNo=@bankNo WHERE staffID = @staffID";
                cmd.Connection = conn;
                cmd.Parameters.AddWithValue("@staffID", staffID);
                cmd.Parameters.AddWithValue("@firstName", firstName);
                cmd.Parameters.AddWithValue("@lastName", lastName);
                cmd.Parameters.AddWithValue("@email", email);
                cmd.Parameters.AddWithValue("@address", address);
                cmd.Parameters.AddWithValue("@dob", dob);
                cmd.Parameters.AddWithValue("@gender", gender);
                cmd.Parameters.AddWithValue("@nationality", nationality);
                cmd.Parameters.AddWithValue("@phoneNo", phoneNo);
                cmd.Parameters.AddWithValue("@staffType", staffType);
                cmd.Parameters.AddWithValue("@bankNo", bankNo);
                rowsinserted = cmd.ExecuteNonQuery();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return rowsinserted;
        }

        //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        // Housekeeping Manager
        //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        public static ArrayList GetScheduledRoomList()
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList roomList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select Distinct * From Room where roomStatus = 'Clean'";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Room room = new Room(Convert.ToInt32(dr["roomID"]),
                                             dr["roomType"].ToString(),
                                             dr["bedType"].ToString(),
                                             Convert.ToInt32(dr["capacity"]),
                                             Convert.ToDouble(dr["roomRates"]),
                                             dr["roomStatus"].ToString(),
                                             dr["remark"].ToString(),
                                             Convert.ToBoolean(dr["smoking"].ToString()));
                    roomList.Add(room);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return roomList;
        }

        public static ArrayList GetScheduledRoomList2()
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList roomList = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select Distinct * From Room where roomStatus = 'Cleaning'";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Room room = new Room(Convert.ToInt32(dr["roomID"]),
                                             dr["roomType"].ToString(),
                                             dr["bedType"].ToString(),
                                             Convert.ToInt32(dr["capacity"]),
                                             Convert.ToDouble(dr["roomRates"]),
                                             dr["roomStatus"].ToString(),
                                             dr["remark"].ToString(),
                                             Convert.ToBoolean(dr["smoking"].ToString()));
                    roomList.Add(room);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return roomList;
        }

        public static bool InsertHousekeepingRecord(HousekeepingRecord hkr)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "insert into HousekeepingRecord " +
                    "(staffID, roomID, assignStaff, dateTime, assignStaffType) values " +
                    "(@staffID, @roomID, @assignStaff, @dateTime, @assignStaffType)";
                comm.Parameters.AddWithValue("@staffID", hkr.staffID);
                comm.Parameters.AddWithValue("@roomID", hkr.roomID);
                comm.Parameters.AddWithValue("@assignStaff", hkr.assignStaff);
                comm.Parameters.AddWithValue("@dateTime", hkr.dateTime);
                comm.Parameters.AddWithValue("@assignStaffType", hkr.assignStaffType);
                int rows = comm.ExecuteNonQuery();
                if (rows > 0)
                    result = true;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static HousekeepingRecord GetHousekeepingRecordByRoomID(int roomID)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            HousekeepingRecord hkr = new HousekeepingRecord();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From HousekeepingRecord Where roomID =" + roomID;
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    hkr.roomID = Convert.ToInt32(dr["roomID"]);
                    hkr.staffID = Convert.ToInt32(dr["staffID"]);
                    hkr.assignStaff = dr["assignStaff"].ToString();
                    hkr.dateTime = Convert.ToDateTime(dr["dateTime"]);
                    hkr.assignStaffType = dr["assignStaffType"].ToString();
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return hkr;
        }

        //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        // Changelog for Edit Customer's Information Manager
        //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        public static ArrayList GetChangeLog()
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            ArrayList changeLog = new ArrayList();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand();
                cmd.CommandText = "Select * From CustomerModification";
                cmd.Connection = conn;

                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    CustomerModification cm = new CustomerModification((dr["cusIC"].ToString()),
                                             Convert.ToInt32(dr["staffID"]),
                                             Convert.ToDateTime(dr["dateTime"].ToString()));
                    changeLog.Add(cm);
                }
                dr.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return changeLog;
        }

        public static bool InsertCustomerModification(CustomerModification cm)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotel_v1.Properties.Settings.DRHotelMngSysConnectionString"].ConnectionString;
                conn.Open();
                comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "insert into CustomerModification " +
                    "(cusIC, staffID, dateTime) values " +
                    "(@cusIC, @staffID, @dateTime)";
                comm.Parameters.AddWithValue("@cusIC", cm.cusIC);
                comm.Parameters.AddWithValue("@staffID", cm.staffID);
                comm.Parameters.AddWithValue("@dateTime", cm.dateTime);
                int rows = comm.ExecuteNonQuery();
                if (rows > 0)
                    result = true;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }
    }
}
